package org.tity.test.dao;

import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.tity.commons.collection.TypedHashMap;
import org.tity.sql.dao.DAO;
import org.tity.sql.dao.procedure.StoredProcedure;
import org.tity.sql.dao.sequence.SequenceType;
import org.tity.sql.data.DataSet;
import org.tity.sql.data.RcdSet;
import org.tity.sql.meta.DBColumnMeta;
import org.tity.sql.meta.DBTableMeta;
import org.tity.sql.meta.DBType;
import org.tity.sql.sql.builder.Insert;
import org.tity.sql.sql.builder.SE;

@RestController
@RequestMapping(value="/dao")
public class BasicDAOController {

 
	
	private boolean enable_sequence = true;

	@Autowired
	private DAO dao;

	/**
	 * 开始一个简单的例子
	 */
	@RequestMapping(value="/start")
	public String start() {
		
//		dao.query();
//		dao.queryPage(sql, size, index)
//		dao.queryRecord(sql)
//		dao.queryString(sql)
		
//		for
//		dao.execute(sql);
//		
//		dao.batchExecute("",list);
//		
//				dao.multiExecute(sqls)
				
				
		
		
		//dao.insertEntitySet(set)
		// 查询单一值
		int totalBeforeInsert = dao.queryInteger("select count(*) from sys_user");
		// 生成一个雪花ID
		long id = dao.getSnowflakeId();
		System.out.println(dao.toString()); // 输出dao最后执行的语句；断点时鼠标移到dao上观察SQL语句
		// 获得一个可执行的insert语句
		Insert insert = dao.insert("sys_user").sets(
				"id", id, 
				"account","name-" + totalBeforeInsert);
		System.out.println(insert); // 输出insert语句，亦可在“表达式”面板观察
		int i = insert.execute();// 执行SQL语句
		// 查询单一值
		int totalAfterInsert = dao.queryInteger("select count(*) from sys_user");
		System.out.println(i);
		return "" + (i == 1 && totalBeforeInsert + 1 == totalAfterInsert);
	}

	/**
	 * 生成ID
	 * */
	@RequestMapping(value = "/id")
	public String id() {
		// UUID
		String id = dao.getUUID();
		System.out.println("uuid:" + id);
		
		//得到一个比UUID更短的唯一ID
		id = dao.getSUID();
		System.out.println("suid:" + id);
		
		/*
		 *  SUID和UUID区别
		 *  长度：SUID更短
		 *  存储：SUID更少
		 *  耗时：SUID别UUID略多，不是极端高并发下课忽略不计
		 *  
		 * */
		
		
		// 雪花ID
		long snowId = dao.getSnowflakeId();
		System.out.println("snowId:" + snowId);

		ArrayList<String> ids=new ArrayList<String>();
		// 序列
		if (enable_sequence) {
			String sName = "user";
			boolean ex = dao.isSequenceExists(sName);
			if (!ex) {
				dao.createSequence(sName, SequenceType.AI, 6);
			}
			dao.setSequenceFetchSize(sName,8); //根据业务的并发度设置合理值，提高ID产生的速度，效果较为显著
			String sVal = null;
			long t0=System.currentTimeMillis();
			for (int i = 0; i < 100; i++) {
				sVal = dao.getNextSequenceValue(sName);
				if(ids.contains(sVal))
				{
					System.err.println("出现重复值:"+sVal);
				}
				ids.add(sVal);
				System.out.println(sVal);
			}
			long t1=System.currentTimeMillis();
			System.out.println("cost："+(t1-t0));
			
			//并发测试
			ArrayList<Thread> ths=new ArrayList<Thread>();
			for (int i = 0; i < 8; i++) { 
				Thread t=new  Thread() {
					 
					public void run() {
						for (int i = 0; i < 100; i++) {
//							dao.query("select user()");
							String sVal = dao.getNextSequenceValue(sName);
							if(ids.contains(sVal))
							{
								System.err.println("出现重复值:"+sVal); 
							}
							ids.add(sVal);
							System.out.println(this.getName()+"-"+sVal);
						}
						this.setName("F"+this.getName());
					};
				};
				t.setName("T"+i);
				ths.add(t);
				t.start();
			}
			
			while(true)
			{
				try {
					Thread.sleep(125);
				} catch (InterruptedException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				boolean isAllFinished=true;
				for (Thread thread : ths) {
					if(thread.getName().startsWith("T")) 
					{
						isAllFinished=false;
						break;
					}
				}
				if(isAllFinished) break;
			}
			long t2=System.currentTimeMillis();
			System.out.println("cost(thread)："+(t2-t1));
		}
		
		
		
		return "idok";
	}
	
	/**
	 * 数据库基本信息
	 * */
	@RequestMapping(value = "/info")
	public String info() {
		//获得数据库标识
		String identity=dao.getDBConnectionIdentity();
		System.out.println("identity:"+identity);  
	 
//		dao.getDBName()
		//获得Schema
		String schema=dao.getSchema();
		System.out.println("schema:"+schema);
	 
		//获得数据库类型
		DBType dbType=dao.getDBType();
		System.out.println(dbType);
		
		DBTableMeta tableMeta=dao.getTableMeta("sys_user");
		DBColumnMeta columnMeta=dao.getTableColumnMeta("sys_user", "name");
		columnMeta.getComment(); //获得全部注释内容
		columnMeta.getLabel(); // 获得前半部分内容作为列标签
		columnMeta.getShortComment(); //获得后半部分内容
 
		return "ok";
	}
	
	
	/**
	 * 存储过程
	 * */
	@RequestMapping(value = "/proc")
	public String proc() {
		if(!enable_sequence) return "ok";
			
		try {
			StoredProcedure p = dao.getStoredProcedure("NEXT_VAL");
			p.declareParameter("ID", Types.VARCHAR);
			p.declareParameter("NUM", Types.INTEGER);
			p.declareOutParameter("SVAL", Types.VARCHAR);
			Map<String, Object> ret = p.execute(TypedHashMap.asMap("ID", "user","NUM",4));
			String val=ret.get("SVAL") + "";
			System.out.println("获得存储过程返回值："+val);
			return "ok";

		} catch (Exception e) {
			e.printStackTrace();
			return "error";
		}
	}
	
	
	/**
	 * 多语句操作以及事务
	 * */
	@RequestMapping(value = "/multi")
	public String multi() {
		String r="";
		//顺利插入
		long id=dao.getSnowflakeId();
		//语句们在一个事务内执行
		int i=dao.multiExecute("insert into sys_user(id,account) values('"+id+"','name-0')","update sys_user set account='name-1' where id='"+id+"'");
		System.out.println("executes="+i);
		String name=dao.queryString("select account from sys_user where id='"+id+"'");
		
		if("name-1".equals(name))
		{
			r+= "2-ok";
		}
		else
		{
			r+= "0-error";
		}
		
		//
		id=dao.getSnowflakeId();
		i=dao.multiExecute(new SE("insert into sys_user(id,account) values(?,?)",id,"name-x"),new SE("update sys_user set account=? where id=?","name-y",id));
		name=dao.queryString("select account from sys_user where id='"+id+"'");
		if("name-y".equals(name))
		{
			r+= ",2-ok";
		}
		else
		{
			r+= ",0-error";
		}
		
		
		//
		id=dao.getSnowflakeId();
		List<Object> sqls=new ArrayList<Object>();
		sqls.add("insert into sys_user(id,account) values('"+id+"','name-0')");
		sqls.add(new SE("update sys_user set account=? where id=?","name-y",id));
		i=dao.multiExecute(sqls);
		name=dao.queryString("select account from sys_user where id='"+id+"'");
		if("name-y".equals(name))
		{
			r+= ",2-ok";
		}
		else
		{
			r+= ",0-error";
		}
		
		return r;
		
	}
	
	/**
	 * 批量操作
	 * */
	@RequestMapping(value = "/batch")
	public String batch() {
		
		//dao.execute("truncate table sys_user");
		
		//以下是一个性能对比
		
		int total=10;
		long t1=System.currentTimeMillis();
		for (int i = 0; i < total; i++) {
			dao.execute("insert into sys_user(id,account) values('"+dao.getSnowflakeId()+"','name-"+i+"')");
		}
		long t2=System.currentTimeMillis();
		System.out.println("耗时-1:"+(t2-t1));
		
		
		ArrayList<Object[]> ps = new ArrayList<Object[]>();
		for (int i = 0; i < total; i++) {
			ps.add(new Object[] {dao.getSnowflakeId(),"name"+i});
		}
		long t3=System.currentTimeMillis();
		dao.batchExecute("insert into sys_user(id,account) values(?,?)",ps);
		long t4=System.currentTimeMillis();
		System.out.println("耗时-1:"+(t4-t3));
		return "ok";
		
		
		
	}
	
	
	
	
	/**
	 * 可执行的SQL语句
	 * */
	@RequestMapping(value = "/execb")
	public String executablesql() {
		
		RcdSet rs=dao.select().from("sys_user").where().and("account like ?","%me0%").topQueryable().query();
		System.out.println(rs.size());
		
		
		return "ok";
	}
	
	/**
	 * 可执行的SQL语句
	 * */
	@RequestMapping(value = "/rcd-page")
	public String rcdpage() {
		
		RcdSet rs=dao.queryPage("select * from sys_user", 4, 1);
		System.out.println(rs.size());
		return rs.size()==4?"ok":"err";
		
	}
	
	
	/**
	 * 查询数据集
	 * */
	@RequestMapping(value = "/dataset")
	public String dataset() {
		
		DataSet ds=dao.queryDataSet("select * from sys_user",true);
		System.out.println(ds.size());
		System.out.println(ds.toJson());
//		dao.execute();
//		dao.query("#list-usr",);
//		dao.getSQLByID(id)
		
		return "ok";
		
	}
	
	
	/**
	 * clob操作
	 * */
	@RequestMapping(value = "/clob")
	public String clob() {
		return "ok";
	}
	
	
	
	/**
	 * 事务
	 * */
	@RequestMapping(value = "/trans")
	public String trans() {
		return "ok";
	}
	
	/**
	 * 事务
	 * */
	@RequestMapping(value = "/insert_key")
	public String insKey() {
		
		System.out.println("kkk");
		Insert ins=new Insert("sys_ictest");
		ins.set("name", "N"+System.currentTimeMillis());
		
		long i=dao.insertAndReturnKey(ins);
		System.out.println(i);
		return "ok";
	}
	

}
